﻿using Quartz.Impl.AdoJobStore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace Hinacom.Scheduler.Impl
{
    /// <summary>
    /// 对Quartz SqlServerDelegate的扩展
    /// </summary>
    public class SqlServerDelegateExt : SqlServerDelegate, IDriverDelegateExt
    {
        protected const string INSERT_JOB_HISTORY_SQL = @"
INSERT INTO [QRTZ_JOB_HISTORY]
    (SCHED_NAME, JOB_NAME, JOB_GROUP, START_TIME_UTC, END_TIME_UTC, [STATUS], RUNNING_LOG)
    VALUES
    (@ScheduleName, @JobName, @JobGroup, @StartTimeUtc, @EndTimeUtc, @Status, @RunningLog)
SELECT @@IDENTITY
";
        protected const string UPDATE_JOB_HISTORY_SQL = @"
UPDATE  [QRTZ_JOB_HISTORY]
SET     END_TIME_UTC = @EndTimeUtc, 
        [STATUS] = @Status,
        RUNNING_LOG = @RunningLog
WHERE   Id = @Id
";

        protected const string GET_JOB_HISTORIES_SQL = @"
SELECT TOP (20) Id, SCHED_NAME, JOB_NAME, JOB_GROUP, START_TIME_UTC, END_TIME_UTC, [STATUS]
FROM [QRTZ_JOB_HISTORY]
WHERE SCHED_NAME = @ScheduleName AND JOB_NAME = @JobName AND JOB_GROUP = @JobGroup";

        protected const string GET_JOB_HISTORIES_SQL_ID_SCOPE = @" AND Id < @LastLoadedId";
        protected const string GET_JOB_HISTORIES_SQL_ORDER = @" ORDER BY Id DESC";

        protected const string GET_JOB_HISTORY_LOG_SQL = @"
SELECT RUNNING_LOG
FROM [QRTZ_JOB_HISTORY]
WHERE Id = @Id
";

        public IEnumerable<JobHistory> GetJobHistories(ConnectionAndTransactionHolder cth, string jobname, string jobgroup, int? lastLoadedId)
        {
            List<JobHistory> hises = new List<JobHistory>();

            string sql = GET_JOB_HISTORIES_SQL;
            if (lastLoadedId.HasValue)
                sql += GET_JOB_HISTORIES_SQL_ID_SCOPE;
            sql += GET_JOB_HISTORIES_SQL_ORDER;

            using (IDbCommand cmd = PrepareCommand(cth, sql))
            {
                AddCommandParameter(cmd, "ScheduleName", "ServerScheduler");
                AddCommandParameter(cmd, "JobName", jobname);
                AddCommandParameter(cmd, "JobGroup", jobgroup);
                if (lastLoadedId.HasValue)
                    AddCommandParameter(cmd, "LastLoadedId", lastLoadedId);

                using (IDataReader rs = cmd.ExecuteReader())
                {
                    List<JobHistory> list = new List<JobHistory>();
                    while (rs.Read())
                    {
                        var his = new JobHistory()
                        {
                            Id = rs.GetInt32(0),
                            JobName = rs.GetString(2),
                            JobGroup = rs.GetString(3),
                            StartTimeUtc = rs.GetDateTime(4),
                            EndTimeUtc = rs.IsDBNull(5) ? new Nullable<DateTime>() : rs.GetDateTime(5),
                            Status = (JobExecutionStatus)rs.GetInt32(6)
                        };
                        list.Add(his);
                    }
                    return list.OrderByDescending(h => h.StartTimeUtc);
                }
            }
        }

        public string GetJobHistoryLog(ConnectionAndTransactionHolder cth, int historyId)
        {
            using (IDbCommand cmd = PrepareCommand(cth, GET_JOB_HISTORY_LOG_SQL))
            {
                AddCommandParameter(cmd, "Id", historyId);

                using (IDataReader rs = cmd.ExecuteReader())
                {
                    if (rs.Read())
                        return rs.GetString(0);
                }
            }
            return null;
        }

        public virtual void InsertJobHistory(ConnectionAndTransactionHolder cth, JobHistory history)
        {
            using (IDbCommand cmd = PrepareCommand(cth, INSERT_JOB_HISTORY_SQL))
            {
                AddCommandParameter(cmd, "ScheduleName", "ServerScheduler");
                AddCommandParameter(cmd, "JobName", history.JobName);
                AddCommandParameter(cmd, "JobGroup", history.JobGroup);
                AddCommandParameter(cmd, "StartTimeUtc", history.StartTimeUtc);
                AddCommandParameter(cmd, "EndTimeUtc", history.EndTimeUtc);
                AddCommandParameter(cmd, "Status", Convert.ToInt32(history.Status));
                AddCommandParameter(cmd, "RunningLog", history.RunningLog);

                history.Id = Convert.ToInt32(cmd.ExecuteScalar());
            }
        }

        public virtual void UpdateJobHistory(ConnectionAndTransactionHolder cth, JobHistory history)
        {
            using (IDbCommand cmd = PrepareCommand(cth, UPDATE_JOB_HISTORY_SQL))
            {
                AddCommandParameter(cmd, "EndTimeUtc", history.EndTimeUtc);
                AddCommandParameter(cmd, "Status", Convert.ToInt32(history.Status));
                AddCommandParameter(cmd, "RunningLog", history.RunningLog);
                AddCommandParameter(cmd, "Id", history.Id.Value);

                cmd.ExecuteScalar();
            }
        }

    }
}
